package com.ning.weixin.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * @author MENG_LI_DONG
 * @version 2018年12月18日 下午11:58:42
 */
@Component
public class AcQueryDao {
	private Logger log = LoggerFactory.getLogger(AcQueryDao.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 获取排名与总分
	 * 
	 * @param openid
	 * @return openid,total_scores,row_no
	 */
	public Map<String, Object> getRankByOpenid(String openid) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT wx.*,c.total_scores,c.sign_in_nums,c.rowNo as rank_num FROM (SELECT tt.openid,tt.total_scores,tt.sign_in_nums,(@rowNum:=@rowNum+1) AS rowNo ");
		sql.append("FROM (select t.openid,sum(t.score) as total_scores,count(t.id) as sign_in_nums from ac_punch t GROUP BY t.openid) tt,");
		sql.append("(SELECT(@rowNum:=0)) b ORDER BY tt.total_scores DESC) c left join ac_wechat_info wx on c.openid=wx.openid where c.openid=?");
		log.debug("sql:"+sql);
		try{
			return jdbcTemplate.queryForMap(sql.toString(),openid);
		} catch(Exception e) {
			return null;
		}
	}

	public List<Map<String, Object>> getPunchList(String openid) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select tt.flag,");
		sql.append(" (select count(id) from ac_punch t where t.openid = ? and t.location_id=tt.id and t.create_time between ? and ?) as sign_nums,");
		sql.append(" (select count(id) from ac_punch ttt where ttt.openid=? and ttt.location_id=tt.id and ttt.create_time between ? and ?) as punch_count,");
		sql.append(" (select count(id) from ac_share_log tttt where tttt.openid=? and tttt.location_id=tt.id and tttt.create_time between ? and ?) as share_count");
		sql.append(" from ac_location tt;");
		log.debug("sql:"+sql);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String s = sdf.format(new Date());
		String string = s + " 00:00:00";
		String endString = s + " 23:59:59";
		SimpleDateFormat sdff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<Map<String,Object>> res = new ArrayList<>();
		try {
			Date startDate = sdff.parse(string);
			Date endDate = sdff.parse(endString);
			List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), openid, startDate, endDate,openid, startDate, endDate,openid, startDate, endDate);
			for(Map<String,Object> map:list){
				Map<String,Object> m = new HashMap<>();
				m.put("location", map.get("flag"));
				m.put("signNums", map.get("sign_nums"));
				Integer punchCount = Integer.parseInt(map.get("punch_count").toString());
				Integer shareCount = Integer.parseInt(map.get("share_count").toString());
				if(punchCount == 0){
					m.put("remainingSignNum", 1);
				} else if(punchCount == 1) {
					if(shareCount>0){
						m.put("remainingSignNum", 1);
					} else {
						m.put("remainingSignNum", 0);
					}
				} else {
					m.put("remainingSignNum", 0);
				}
				res.add(m);
			}
		} catch (ParseException e) {
			e.printStackTrace();
			return new ArrayList<Map<String, Object>>();
		}
		return res;
	}

	public List<Map<String, Object>> getTodayPunchList(String openid,Long locationId) {
		String sql = "select * from ac_punch t where t.openid=? and location_id=? and t.create_time between ? and ?";
		log.debug("sql:"+sql);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String s = sdf.format(new Date());
		String string = s + " 00:00:00";
		String endString = s + " 23:59:59";
		SimpleDateFormat sdff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			Date startDate = sdff.parse(string);
			Date endDate = sdff.parse(endString);
			return jdbcTemplate.queryForList(sql, openid, locationId,startDate, endDate);
		} catch (ParseException e) {
			e.printStackTrace();
			return new ArrayList<Map<String, Object>>();
		}
	}

	public int getTodayShareCount(String openid, Long locationId) {
		String sql = "select count(id) from ac_share_log t where t.openid=? and t.location_id=? and t.create_time between ? and ?";
		log.debug("sql:"+sql);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String s = sdf.format(new Date());
		String string = s + " 00:00:00";
		String endString = s + " 23:59:59";
		SimpleDateFormat sdff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			Date startDate = sdff.parse(string);
			Date endDate = sdff.parse(endString);
			return jdbcTemplate.queryForObject(sql, new Object[] { openid,
					locationId, startDate, endDate }, Integer.class);
		} catch (ParseException e) {
			e.printStackTrace();
			return 0;
		}
	}
	public List<Map<String,Object>> getRanksByPage(int pageIndex, int pageSize){
		/**
		 * SELECT wx.*,c.total_scores,c.sign_in_nums,c.rowNo as rank_num FROM (SELECT tt.openid,tt.total_scores,tt.sign_in_nums,(@rowNum:=@rowNum+1) AS rowNo
FROM (select t.openid,sum(t.score) as total_scores,count(t.id) as sign_in_nums from ac_punch t GROUP BY t.openid) tt,
(SELECT(@rowNum:=0)) b ORDER BY tt.total_scores DESC) c left join ac_wechat_info wx on c.openid=wx.openid;
		 */
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT wx.*,c.total_scores,c.sign_in_nums,c.rowNo as rank_num FROM (SELECT tt.openid,tt.total_scores,tt.sign_in_nums,(@rowNum:=@rowNum+1) AS rowNo ");
		sql.append("FROM (select t.openid,sum(t.score) as total_scores,count(t.id) as sign_in_nums from ac_punch t GROUP BY t.openid) tt, ");
		sql.append("(SELECT(@rowNum:=0)) b ORDER BY tt.total_scores DESC) c left join ac_wechat_info wx on c.openid=wx.openid order by c.total_scores desc limit ?,?");
		log.debug("sql:"+sql);
		return jdbcTemplate.queryForList(sql.toString(), pageIndex*pageSize,pageSize);
	}
	public int getRanksByPageCount(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(*) FROM (SELECT tt.openid,tt.total_scores,tt.sign_in_nums,(@rowNum:=@rowNum+1) AS rowNo ");
		sql.append("FROM (select t.openid,sum(t.score) as total_scores,count(t.id) as sign_in_nums from ac_punch t GROUP BY t.openid) tt,");
		sql.append("(SELECT(@rowNum:=0)) b ORDER BY tt.total_scores DESC) c left join ac_wechat_info wx on c.openid=wx.openid");
		log.debug("sql:"+sql);
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
	}
	
}
